from selenium.webdriver import Chrome, ChromeOptions
import requests
import pymysql
import time
import json
import traceback
import sys

def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                           user="root",
                           password="123456",
                           db="cov",
                           charset="utf8")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()


def get_tencent_data():
    """
    :return: 返回历史数据和当日详细数据
    """
    url = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5'
    url_his = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_other'  # 加上这个history

    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36"    }
    r = requests.get(url, headers)#发起请求
    res = json.loads(r.text)  # json字符串转字典
    data_all = json.loads(res['data'])

    # 再加上history的配套东东++++++++
    r_his = requests.get(url_his, headers)#发起请求
    res_his = json.loads(r_his.text)
    data_his = json.loads(res_his['data'])

    history = {}  # 历史数据
    for i in data_his["chinaDayList"]:
        ds = "2020." + i["date"]
        tup = time.strptime(ds, "%Y.%m.%d")
        ds = time.strftime("%Y-%m-%d", tup)  # 改变时间格式,不然插入数据库会报错，数据库是datetime类型
        confirm = i["confirm"]
        suspect = i["suspect"]
        heal = i["heal"]
        dead = i["dead"]
        history[ds] = {"confirm": confirm, "suspect": suspect, "heal": heal, "dead": dead}
    for i in data_his["chinaDayAddList"]:
        ds = "2020." + i["date"]
        tup = time.strptime(ds, "%Y.%m.%d")
        ds = time.strftime("%Y-%m-%d", tup)
        confirm = i["confirm"]
        suspect = i["suspect"]
        heal = i["heal"]
        dead = i["dead"]
        history[ds].update({"confirm_add": confirm, "suspect_add": suspect, "heal_add": heal, "dead_add": dead})


    details = []  # 当日详细数据
    update_time = data_all["lastUpdateTime"]
    data_country = data_all["areaTree"]  # list 25个国家
    data_province = data_country[0]["children"]  # 中国各省
    for pro_infos in data_province:
        province = pro_infos["name"]  # 省名
        for city_infos in pro_infos["children"]:
            city = city_infos["name"]
            confirm = city_infos["total"]["confirm"]
            confirm_add = city_infos["today"]["confirm"]
            heal = city_infos["total"]["heal"]
            dead = city_infos["total"]["dead"]
            details.append([update_time, province, city, confirm, confirm_add, heal, dead])
    return history, details
def get_tencent_data1():
    url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
    res = requests.get(url)
    d = json.loads(res.text)
    data_all = json.loads(d["data"])
    details = []  # 当日详细数据
    update_time = data_all["lastUpdateTime"]
    data_country = data_all["areaTree"]  # list 25个国家
    data_province = data_country[0]["children"]  # 中国各省
    i = 0
    for province in data_province:  # 省名
        #         print(province["name"])
        #         print(province["total"])
        name = province["name"]  # 省名
        nowConfirm = province["total"]["nowConfirm"]  # 现有确诊
        confirm = province["total"]["confirm"]  # 累计确诊
        suspect = province["total"]["suspect"]  # 现有疑似
        dead = province["total"]["dead"]  # 累计死亡
        deadRate = province["total"]["deadRate"]  # 死亡率
        heal = province["total"]["heal"]  # 累计治愈
        healRate = province["total"]["healRate"]  # 治愈率
        details.append([update_time, name, nowConfirm, confirm, suspect, dead, deadRate, heal, healRate])
    return details


def update_province():
    """
    更新 province 表
    :return:
    """
    cursor = None
    conn = None
    try:
        li = get_tencent_data1()  # 0 是历史数据字典,1 最新详细数据列表
        conn, cursor = get_conn()
        sql = "insert into province(update_time,name,nowConfirm,confirm,suspect,dead,deadRate,heal,healRate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select update_time from province order by id desc limit 1)'  # 对比当前最大时间戳
        cursor.execute(sql_query, li[0][0])
        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新各省最新数据")
            for item in li:
                
                cursor.execute(sql, item)
            conn.commit()  # 提交事务 update delete insert操作
            print(f"{time.asctime()}更新各省最新数据完毕")
        else:
            print(f"{time.asctime()}已是各省最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)

def get_baidu_hot():
    """
    :return: 返回百度疫情热搜
    """
    option = ChromeOptions()  # 创建谷歌浏览器实例
    option.add_argument("--headless")  # 隐藏浏览器
    option.add_argument('--no-sandbox')

    url = "https://voice.baidu.com/act/virussearch/virussearch?from=osari_map&tab=0&infomore=1"
    browser = Chrome(r"C:\Users\xuton\AppData\Local\Google\Chrome\Application\chromedriver", options=option)
    browser.get(url)
    # 找到展开按钮
    dl = browser.find_element_by_xpath('//*[@id="ptab-0"]/div/div[1]/section/div')
    dl.click()
    time.sleep(1)
    # 找到热搜标签
    c = browser.find_elements_by_xpath('//*[@id="ptab-0"]/div/div[1]/section/a/div/span[2]')
    context = [i.text for i in c]  # 获取标签内容
    #print(context)
    return context


def update_hotsearch():
    """
    将疫情热搜插入数据库
    :return:
    """
    cursor = None
    conn = None
    try:
        context = get_baidu_hot()
        print(f"{time.asctime()}开始更新热搜数据")
        conn, cursor = get_conn()
        sql = "insert into hotsearch(dt,content) values(%s,%s)"
        ts = time.strftime("%Y-%m-%d %X")
        for i in context:
            cursor.execute(sql, (ts, i))  # 插入数据
        conn.commit()  # 提交事务保存数据
        print(f"{time.asctime()}数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)

def update_details():
    """
    更新 details 表
    :return:
    """
    cursor = None
    conn = None
    try:
        li = get_tencent_data()[1]  #  0 是历史数据字典,1 最新详细数据列表
        conn, cursor = get_conn()
        sql = "insert into details(update_time,province,city,confirm,confirm_add,heal,dead) values(%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select update_time from details order by id desc limit 1)' #对比当前最大时间戳
        cursor.execute(sql_query,li[0][0])
        if not cursor.fetchone()[0]:#获取查询结果
            print(f"{time.asctime()}开始更新最新数据")
            for item in li:
                cursor.execute(sql, item)
            conn.commit()  # 提交事务 update delete insert操作
            print(f"{time.asctime()}更新最新数据完毕")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_history():
    """
    更新历史数据
    :return:
    """
    cursor = None
    conn = None
    try:
        dic = get_tencent_data()[0]  #  0 是历史数据字典,1 最新详细数据列表
        print(f"{time.asctime()}开始更新历史数据")
        conn, cursor = get_conn()
        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select confirm from history where ds=%s"
        for k, v in dic.items():
            # item 格式 {'2020-01-13': {'confirm': 41, 'suspect': 0, 'heal': 0, 'dead': 1}
            if not cursor.execute(sql_query, k):
                cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("suspect"),
                                     v.get("suspect_add"), v.get("heal"), v.get("heal_add"),
                                     v.get("dead"), v.get("dead_add")])
        conn.commit()  # 提交事务 update delete insert操作
        print(f"{time.asctime()}历史数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


#世界
def tencent():
    url_foreign = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_foreign'

    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36"
    }  # 反扒

    url_h5 = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5'  # 只为爬取时间
    r_h5 = requests.get(url_h5, headers)
    res_url_h5 = json.loads(r_h5.text)  # json字符串转字典
    data_all_h5 = json.loads(res_url_h5['data'])

    url_foreign = requests.get(url_foreign, headers)  ##发起请求
    res_foreign = json.loads(url_foreign.text)  # json字符串转字典
    data_all_foreign = json.loads(res_foreign['data'])

    foreignList = []
    lastUpdateTim = data_all_h5["lastUpdateTime"]
    data_foreignList = data_all_foreign["foreignList"]
    for for_infos in data_foreignList:
        name = for_infos["name"]
        continent = for_infos["continent"]
        date = "2020." + for_infos["date"]
        tup = time.strptime(date, "%Y.%m.%d")
        date = time.strftime("%Y-%m-%d", tup)
        confirmAdd = for_infos["confirmAdd"]
        confirm = for_infos["confirm"]
        suspect = for_infos["suspect"]
        dead = for_infos["dead"]
        heal = for_infos["heal"]
        nowConfirm = for_infos["nowConfirm"]
        foreignList.append([lastUpdateTim, name, continent, date, confirmAdd, confirm, suspect, dead, heal,nowConfirm])

    globalStatis = {}
    lastUpdateTime = data_all_foreign["globalStatis"]["lastUpdateTime"]
    nowConfirm = data_all_foreign["globalStatis"]["nowConfirm"]
    confirm = data_all_foreign["globalStatis"]["confirm"]
    heal = data_all_foreign["globalStatis"]["heal"]
    dead = data_all_foreign["globalStatis"]["dead"]
    nowConfirmAdd = data_all_foreign["globalStatis"]["nowConfirmAdd"]
    confirmAdd = data_all_foreign["globalStatis"]["confirmAdd"]
    healAdd = data_all_foreign["globalStatis"]["healAdd"]
    deadAdd = data_all_foreign["globalStatis"]["deadAdd"]
    globalStatis[lastUpdateTime] = {"nowConfirm": nowConfirm, "confirm": confirm, "heal": heal,
                                    "dead": dead, "nowConfirmAdd": nowConfirmAdd, "confirmAdd": confirmAdd,
                                    "healAdd": healAdd, "deadAdd": deadAdd
                                    }

    globalDailyHistory = {}
    for i in data_all_foreign["globalDailyHistory"]:
        date = "2020." + i["date"]
        tup = time.strptime(date, "%Y.%m.%d")
        date = time.strftime("%Y-%m-%d", tup)
        confirm = i["all"]["confirm"]
        dead = i["all"]["dead"]
        heal = i["all"]["heal"]
        newAddConfirm = i["all"]["newAddConfirm"]
        deadRate = i["all"]["deadRate"]
        healRate = i["all"]["healRate"]
        globalDailyHistory[date] = {"confirm": confirm, "dead": dead, "heal": heal, "newAddConfirm": newAddConfirm,
                                    "deadRate": deadRate, "healRate": healRate
                                    }

    return foreignList,globalStatis,globalDailyHistory



def updateForeignList():
    cursor = None
    conn = None
    try:
        listData = tencent()[0]
        conn, cursor = get_conn()
        sql = "insert into foreignList(lastUpdateTime,name,continent,date,confirmAdd," \
              "confirm,suspect,dead,heal,nowConfirm) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = 'select %s=(select lastUpdateTime from foreignList order by id desc limit 1)'
        cursor.execute(sql_query, listData[0][0])
        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新不含中国的海外国家最后一次更新的详细数据")
            for item in listData:
                print(listData)
                cursor.execute(sql, item)
            conn.commit()
            print(f"{time.asctime()}更新不含中国的海外国家最后一次更新的详细数据完毕")
        else:
            print(f"{time.asctime()}已是不含中国的海外国家最后一次更新的详细数据最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def updateGlobalStatis():
    cursor = None
    conn = None
    try:
        dictData = tencent()[1]
        conn, cursor = get_conn()
        sql = "insert into globalStatis values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select nowConfirm from globalStatis where lastUpdateTime=%s"
        for key, value in dictData.items():
            if not cursor.execute(sql_query, key):
                cursor.execute(sql, [key,value.get("nowConfirm"),value.get("confirm"),
                                     value.get("heal"),value.get("dead"),value.get("nowConfirmAdd"),
                                     value.get("confirmAdd"),value.get("healAdd"),value.get("deadAdd")
])
        conn.commit()
        print(f"{time.asctime()}全球累计总数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def updateGlobalDailyHistory():
    cursor = None
    conn = None
    try:
        dic = tencent()[2]
        print(f"{time.asctime()}开始更新按时间分条的全球累计数据")
        conn, cursor = get_conn()
        sql = "insert into globalDailyHistory values(%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select confirm from globalDailyHistory where date=%s"
        for key, value in dic.items():
            if not cursor.execute(sql_query, key):
                cursor.execute(sql, [key,value.get("confirm"),value.get("dead"),value.get("heal"),
                                     value.get("newAddConfirm"),value.get("deadRate"),value.get("healRate")
])
        conn.commit()
        print(f"{time.asctime()}按时间分条的全球累计数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)

if __name__ == "__main__":

    update_history()
    update_details()
    update_hotsearch()
    update_province()
    updateForeignList()  # 不含中国的海外各国家疫情数据
    updateGlobalStatis()  # 全球总疫情数据
    updateGlobalDailyHistory()  # 历史数据

